import uuid
from datetime import datetime
from src.label.label_models import VvvUserLoginLog
import chardet
import re
import pandas as pd
from sqlalchemy.orm import Session
from sqlalchemy.dialects.postgresql import insert

from src.label import label_logger as logging
from src.config.database import SessionPgTest
from src.label.label_schemas import VvvUserLoginLogCreate


def safe_int(value, default=0):
    try:
        return int(value)
    except (TypeError, ValueError):
        return default


def safe_datetime(value, default=None):
    if isinstance(value, datetime):
        return value
    if isinstance(value, str):
        if value.endswith('+08'):
            value = value.replace('+08', '+0800')
        try:
            # 移除非法字符
            value = value.replace('.-1f', '')
            # 尝试解析 MM/DD/YYYY HH:MM:SS 格式
            return datetime.strptime(value, "%d/%m/%Y %H:%M:%S%z")
        except ValueError:
            pass
    return default


def excel_to_datetime(date_str):
    # 使用正则提取并修复时间部分
    match = re.match(r'(\d{4}-\d{2}-\d{2}) (\d{2}):(\d{2})(\d{2}):(\d{2})', date_str)
    if match:
        date_part = match.group(1)
        hour = match.group(2)
        minute = match.group(3)
        second = match.group(4)
        fixed_str = f"{date_part} {hour}:{minute}:{second}"
        return datetime.strptime(fixed_str, "%Y-%m-%d %H:%M:%S")
    return None


def load_csv(file_path, chunk_size=10000, max_rows=1000000):
    """
    流式读取 CSV 文件并写入数据库
    :param file_path: CSV 文件路径
    :param chunk_size: 每次读取行数
    :param max_rows: 最多读取行数（None 表示读取全部剩余行）
    """
    db: Session = SessionPgTest()
    # 1. 自动检测文件编码
    with open(file_path, 'rb') as f:
        result = chardet.detect(f.read(10000))
    encoding = result.get('encoding', 'utf-8')
    logging.info(f"使用编码格式：{encoding} 读取文件")

    # 2. 流式分块读取 CSV（每次读取最多 10000 行）
    total_processed = 0  # 已处理总行数

    for chunk in pd.read_csv(file_path, encoding=encoding, chunksize=chunk_size, on_bad_lines='skip',
                             low_memory=False):
        logging.info(f"正在处理 {chunk.shape[0]} 行数据...")
        db_objects = []
        for _, row in chunk.iterrows():
            if total_processed >= max_rows:
                logging.info(f"已达到最大行数限制：{max_rows}，停止处理。")
                break

            try:
                data = VvvUserLoginLogCreate(
                    id=str(uuid.uuid4()),
                    company_type=str(row.get('company_type')) if pd.notna(row.get('company_type')) else None,
                    user_id=str(row.get('usr_id')) if pd.notna(row.get('usr_id')) else None,
                    register_channel=str(row.get('register_channel')) if pd.notna(
                        row.get('register_channel')) else None,
                    register_time=safe_datetime(row.get('register_time')) if pd.notna(
                        row.get('register_time')) else None,
                    channel_client=str(row.get('channel_client')) if pd.notna(row.get('channel_client')) else None,
                    login_type=str(row.get('login_type')) if pd.notna(row.get('login_type')) else None,
                    login_time=excel_to_datetime(row.get('login_time')) if pd.notna(row.get('login_time')) else None,
                    resolved=0
                )
                data = data.model_dump()
                # 转换为 ORM 对象
                db_obj = convert_to_vvv_user_login_log(data)
                db_objects.append(db_obj)
                total_processed += 1
            except Exception as e:
                logging.error(f"跳过错误行：{row.to_dict()}，错误：{e}")
                continue
        # 4. 批量插入数据库
        if db_objects:
            # 使用能够忽略id冲突的方法
            # db.add_all(db_objects)
            stmt = insert(VvvUserLoginLog).on_conflict_do_nothing(index_elements=['id'])
            db.execute(stmt, [obj.to_dict() for obj in db_objects])
            db.commit()
            logging.info(f"已批量插入（忽略冲突）{len(db_objects)} 条记录")
            db_objects.clear()

        if total_processed >= max_rows:
            break


def convert_to_vvv_user_login_log(data: VvvUserLoginLogCreate) -> VvvUserLoginLog:
    # 提取 sys.query 和 answer
    res = VvvUserLoginLog(
        id=data.get('id'),
        company_type=data.get('company_type'),
        user_id=data.get('user_id'),
        register_channel=data.get('register_channel'),
        register_time=data.get('register_time'),
        channel_client=data.get('channel_client'),
        login_type=data.get('login_type'),
        login_time=data.get('login_time'),
        resolved=0
    )
    return res


if __name__ == '__main__':
    input_file = 'D:\\2-work\\25-创意组\\标签用户体系\\标签体系原始数据\\船视宝2025年用户登录日志.csv'
    load_csv(input_file)
    # print(safe_datetime("14/5/2025 11:12:53.-1f+08"))
    # print(excel_to_datetime("45840.8641782407"))
